




ALTER    proc sp_put_lot_in_next_step
@lot_id bigint,
@lot_number varchar(20),
@error_code  varchar(50) OUTPUT,
@return_lot_id bigint OUTPUT
AS
begin
	declare @process_step_id bigint
	declare @location_id int
	declare @ps_type_name varchar(20)
	declare @current_lot_id bigint
	declare @err_code varchar(50)
--cost calculation not yet done need to implement
	select @process_step_id = ProcessStep_ID, @location_id = Location_ID from Lot_Shipping_Instructions_Table where Lot_ID = @lot_id
	
	if @process_step_id is not null and @location_id is not null
	begin
		select @current_lot_id = Lot_ID from Lot_Table where ProcessStep_ID = @process_step_id and Location_ID = @location_id and Lot_Number = @lot_number and Lot_Current_Status = 'Running'

		if @current_lot_id is null
		begin
			insert into Lot_Table(ProcessStep_ID, Location_ID, Lot_Number, Lot_Current_Status) 
			values(@process_step_id, @location_id, @lot_number, 'Running')
		
			set @current_lot_id = (select @@identity)
			update Lot_Table set Lot_Current_Status = 'finished' where Lot_ID = @lot_id
			set @err_code = 'SUCCESS'
		end
	end
	else
	begin
		select @current_lot_id = Lot_ID from Lot_Shipping_Instructions_Table where Lot_ID = @lot_id and Status = 'Ongoing'
		if @current_lot_id is null
		begin
			insert into Lot_Shipping_Instructions_Table(Lot_ID, Status)
			values (@lot_id, 'Ongoing')
			update Lot_Table set Lot_Current_Status = 'Queue' where Lot_ID = @lot_id
			set @current_lot_id = 0
			set @err_code = 'SUCCESS'
		end
	end

set @error_code = @err_code
set @return_lot_id = @current_lot_id
select @error_code, @return_lot_id
return
end




